package cn.gson.crm.model.dao;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.po.CallbackPo;

public class CallbackDao {
	
	private int bands = 5;
	
	public List<CallbackPo> getAllCallbackList(int page, int id , String stucompany) throws SQLException{
		int start = (page - 1) * bands;
		List<Object> params = new ArrayList<>();
		List<Map<String, Object>> listData = null;
		String sql = "SELECT student.student_name,callback.* FROM callback "
				+ "LEFT OUTER JOIN student ON student.student_id = callback.callback_student_id";
		sql = buildSqlParams(params, sql, id, stucompany);
		sql +=  " order by callback.callback_time desc LIMIT ? , ?";
		params.add(start);
		params.add(bands);
		listData = Jdbc.getInstace().executeQueryForList(sql,params.toArray());
		List<CallbackPo> CallbackPoPoList = null;
		if(listData!=null){
			CallbackPoPoList = buildCallbackPo(listData);
		}
		return CallbackPoPoList;
	}
	
	public boolean updateCallback(String callbackId, String callbackCompany,String callbackMoney,String callbackPosition,String callbackContent,String callbackTime) throws SQLException{
		String sql = "update callback set callback_company = ?,callback_money= ?,callback_position = ?, callback_content = ?,callback_time =? where callback_id = ?";
		return Jdbc.getInstace().executeUpdate(sql, callbackCompany,callbackMoney,callbackPosition,callbackContent,callbackTime,callbackId);
	}
	public CallbackPo selectCallback(String id) throws SQLException{
		String sql = "SELECT student.student_name,callback.* FROM callback "
				+ "LEFT OUTER JOIN student ON student.student_id = callback.callback_student_id"
				+" where callback_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, id);
		CallbackPo callbackPo = new CallbackPo();
		if(map != null){
			 callbackPo = buildACallbackPo(map);
		}
		return callbackPo;
	}
	public boolean insertCallback(String callbackCompany,String callbackMoney, String stuId, String callbackPosition, String callbackContent, String callbackTime) throws SQLException{
		String sql = "INSERT INTO callback (callback_company,callback_money,callback_position,callback_content,callback_time,callback_student_id) VALUES(?,?,?,?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql,callbackCompany, callbackMoney, callbackPosition,callbackContent, callbackTime, stuId);
	}
	
 	public int getCallbackPageSum() throws SQLException {
		String sql = "SELECT COUNT(*) as sum from callback";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql);
		int sum = 0;
		if (map != null) {
			sum = Integer.parseInt(map.get("sum").toString());
			sum = sum % bands > 0 ? sum / bands + 1 : sum / bands;
		}
		return sum;
	}
	protected CallbackPo buildACallbackPo(Map<String, Object> data){
		CallbackPo  callbackPo= new CallbackPo();
		callbackPo.setCallbackId(type(data.get("callback_id")));
		callbackPo.setCallbackCompany((String)data.get("callback_company"));
		callbackPo.setCallbackContent((String)data.get("callback_content"));
		callbackPo.setCallbackMoney(type(data.get("callback_money")));
		callbackPo.setCallbackPosition((String)data.get("callback_position"));
		callbackPo.setCallbackStudentId(type(data.get("callback_student_id")));
		callbackPo.setStudentName((String)data.get("student_name"));
		String s = data.get("callback_time").toString().split(" ")[0];
		callbackPo.setCallbackTime(Date.valueOf(s));
	return callbackPo;
	}
	protected List<CallbackPo> buildCallbackPo(List<Map<String, Object>> listData){
		List<CallbackPo> callbackPoList = new ArrayList<>();
		for (Map<String, Object> data : listData) {
			CallbackPo  callbackPo= new CallbackPo();
			callbackPo.setCallbackId(type(data.get("callback_id")));
			callbackPo.setCallbackCompany((String)data.get("callback_company"));
			callbackPo.setCallbackContent((String)data.get("callback_content"));
			callbackPo.setCallbackMoney(type(data.get("callback_money")));
			callbackPo.setCallbackPosition((String)data.get("callback_position"));
			callbackPo.setCallbackStudentId(type(data.get("callback_student_id")));
			callbackPo.setStudentName((String)data.get("student_name"));
			String s = data.get("callback_time").toString().split(" ")[0];
			callbackPo.setCallbackTime(Date.valueOf(s));
  			callbackPoList.add(callbackPo);
		}
		return callbackPoList;
		}
	
	public int getStuId(String name) throws SQLException{
		String sql = "select office_student_id from office where office_student_id in "
				+ "(select student_id from student where student_name = ?)";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, name);
		int id = 0;
		if(map != null){
			id = type(map.get("office_student_id"));
		}
		return id;
	}
	
	/**
	 * 字符串转为int
	 * */
	protected int type(Object object) {
		return Integer.parseInt(object.toString());
	}
	
	private String buildSqlParams(List<Object> params, String sql, int id, String stucompany) {
		if (id>0 || !StringUtils.isNullOrEmpty(stucompany)
				|| !StringUtils.isNullOrEmpty(stucompany)) {
			sql += " where";
		}
		if (id>0) {
			sql += " callback.callback_student_id = ? ";
			params.add(id);
			if (!StringUtils.isNullOrEmpty(stucompany)) {
				sql += " and ";
			}
		}

		if (!StringUtils.isNullOrEmpty(stucompany)) {
			sql += " callback.callback_company like ? ";
			params.add("%" + stucompany + "%");
		}
		return sql;
	}
	}

